﻿using Microsoft.Extensions.Logging;
using PHMEE.Entities.DaqInfo.TdDevices;
using PHMEE.IRepositories.DaqInfo.TdDevices;
using PHMEE.Managers.BaseInfo.Vessels;
using PHMEE.ToolKits;
using System.Linq;
using Volo.Abp;
using Volo.Abp.Domain.Repositories;
using Volo.Abp.Guids;
using Volo.Abp.Uow;

namespace PHMEE.Admin
{
    /// <summary>
    /// 操作超级表，与创建关系
    /// </summary>
    public class TdDeviceAppService : PHMEEAdminAppService, ITdDeviceAppService
    {
        private ILogger<TdDeviceAppService> _logger;
        private IGuidGenerator _guidGenerator;
        private readonly ITdDevicePointRelationRepository _tdDevicePointRelationRepository;
        private readonly ITdDataBaseRepository _tdDatabaseRepository;
        private readonly ITdDeviceTypeRepository _tdDeviceTypeRepository;
        private readonly ITdDeviceAttributeRepository _tdDeviceAttributeRepository;
        private readonly ITdDeviceInstanceRepository _tdDeviceInstanceRepository;
        private readonly IPointRepository _pointRepository;
        private readonly ITdDeviceManager _tdDeviceManager;
        private readonly ITdSqlScriptRepository _tdSqlScriptRepository;
        private readonly TdDataSeedManagerFactory _factory;

        public TdDeviceAppService(
            ILogger<TdDeviceAppService> logger,
            IGuidGenerator guidGenerator,
            ITdDevicePointRelationRepository tdDevicePointRelationRepository,
            ITdDataBaseRepository tdDatabaseRepository,
            ITdDeviceTypeRepository tdDeviceTypeRepository,
            ITdDeviceAttributeRepository tdDeviceAttributeRepository,
            ITdDeviceInstanceRepository tdDeviceInstanceRepository,
            IPointRepository pointRepository,
            ITdDeviceManager tdDeviceManager,
            ITdSqlScriptRepository tdSqlScriptRepository,
            TdDataSeedManagerFactory factory)
        {
            _logger = logger;
            _guidGenerator = guidGenerator;
            _tdDevicePointRelationRepository = tdDevicePointRelationRepository;
            _tdDatabaseRepository = tdDatabaseRepository;
            _tdDeviceTypeRepository = tdDeviceTypeRepository;
            _tdDeviceAttributeRepository = tdDeviceAttributeRepository;
            _tdDeviceInstanceRepository = tdDeviceInstanceRepository;
            _pointRepository = pointRepository;
            _tdDeviceManager = tdDeviceManager;
            _tdSqlScriptRepository = tdSqlScriptRepository;
            _factory = factory;
        }
        /// <summary>
        /// 创建数据库
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<Guid> CreateDataBaseAsync(CreateDatabaseDto input)
        {
            // 检查名称是否已存在
            var existingDatabase = await _tdDatabaseRepository.FirstOrDefaultAsync(x => x.Name == input.Name);
            if (existingDatabase != null)
            {
                throw new UserFriendlyException("当前数据已存在.")
                    .WithData("Name", input.Name);
            }

            // 创建数据库实体
            var database = new TdDatabase(_guidGenerator.Create())
            {
                Name = input.Name,
                Description = input.Description
            };

            await _tdDatabaseRepository.InsertAsync(database);
            return database.Id;
        }
        /// <summary>
        /// 获取所有数据库
        /// </summary>
        /// <returns></returns>
        public async Task<List<DatabaseDto>> GetAllDatabasesAsync()
        {
            var databases = await _tdDatabaseRepository.GetListAsync();
            return databases.Select(db => new DatabaseDto
            {
                Id = db.Id,
                Name = db.Name,
                Description = db.Description
            }).ToList();
        }
        /// <summary>
        /// 根据数据库名称查询数据库是否存在
        /// </summary>
        /// <param name="dbName"></param>
        /// <returns></returns>
        public async Task<bool> GetDbNameAsync(string dbName)
        {

            var existingDatabase = await _tdDatabaseRepository.FirstOrDefaultAsync(x => x.Name == dbName);
            if (existingDatabase != null)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 删除数据库(会删除对应所有超级表）
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task DeleteDataBaseAsync(Guid id)
        {
            // 检查数据库是否存在
            var database = await _tdDatabaseRepository.FirstOrDefaultAsync(d => d.Id == id);
            if (database == null)
            {
                throw new UserFriendlyException("未找到当前数据库.", "指定的数据库不存在.")
                    .WithData("DatabaseId", id);
            }
            await _tdDatabaseRepository.DeleteAsync(id);
        }
        /// <summary>
        /// 创建超级表
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<Guid> CreateSuperTableAsync(CreateSuperTableDto input)
        {
            // 检查数据库是否存在
            var database = await _tdDatabaseRepository.FirstOrDefaultAsync(d => d.Id == input.DatabaseId);
            if (database == null)
            {
                throw new UserFriendlyException("未找到当前数据库", "指定的数据库不存在.")
                    .WithData("DatabaseId", input.DatabaseId);
            }

            // 检查超级表名称是否重复
            var existingSuperTable = await _tdDeviceTypeRepository.FirstOrDefaultAsync(x => x.Name == input.Name && x.DatabaseId == input.DatabaseId);
            if (existingSuperTable != null)
            {
                throw new UserFriendlyException(L[PHMEEDomainErrorCodes.TdSuperTableNameAlreadyExist], PHMEEDomainErrorCodes.TdSuperTableNameAlreadyExist).WithData("Name", input.Name);
            }

            // 创建超级表
            var superTable = new TdDeviceType(_guidGenerator.Create())
            {
                DatabaseId = input.DatabaseId,
                Name = input.Name,
                Description = input.Description
            };
            await _tdDeviceTypeRepository.InsertAsync(superTable);

            return superTable.Id;
        }
        /// <summary>
        /// 根据数据库ID获取超级表
        /// </summary>
        /// <param name="databaseId">TD数据库id</param>
        /// <returns></returns>
        public async Task<List<SuperTableDto>> GetSuperTablesByDatabaseIdAsync(Guid databaseId)
        {
            var superTables = await _tdDeviceTypeRepository.GetSuperByDataBaseTableIdAsync(databaseId);

            return superTables.Select(superTable => new SuperTableDto
            {
                Id = superTable.Id,
                Name = superTable.Name,
                Description = superTable.Description
            }).ToList();
        }
        /// <summary>
        /// 删除超级表（会删除对应所有列和子表）
        /// </summary>
        /// <param name="superTableId"></param>
        /// <returns></returns>
        public async Task DeleteSuperTableAsync(Guid superTableId)
        {
            // 检查超级表是否存在
            var superTable = await _tdDeviceTypeRepository.FirstOrDefaultAsync(t => t.Id == superTableId);
            if (superTable == null)
            {
                throw new UserFriendlyException("未找到当前超级表", "指定的超级表不存在.")
                    .WithData("SuperTableId", superTableId);
            }

            // 删除超级表（级联删除所有关联的属性和子表）
            await _tdDeviceTypeRepository.DeleteAsync(superTable);
        }
        /// <summary>
        /// 批量创建超级表的属性和更新属性
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        [UnitOfWork]
        public async Task CreateUpdateAttributesForSuperTableAsync(CreateAndUpdateAttributesDto input)
        {
            // 验证传入的属性集合中是否有重复的列名
            var duplicateNames = input.Attributes.GroupBy(attr => attr.Name).Where(g => g.Count() > 1).Select(g => g.Key).ToList();
            if (duplicateNames.Any())
            {
                throw new UserFriendlyException(L[PHMEEDomainErrorCodes.TdAttributesNameDuplicate], PHMEEDomainErrorCodes.TdAttributesNameDuplicate)
                    .WithData("DuplicateNames", string.Join(", ", duplicateNames));
            }
            //查出所有列
            var existingAttributes = await _tdDeviceAttributeRepository.GetAttributesBySuperTableIdAsync(input.SuperTableId);

            // 删除当前超级表的所有列
            await _tdDeviceAttributeRepository.DeleteManyAsync(existingAttributes);

            var newAttributes = new List<TdDeviceAttribute>();

            // 检查前端传入的属性集合中是否已经包含了 "ts"
            var tsAttribute = input.Attributes.FirstOrDefault(attr => attr.Name == "ts");
            if (tsAttribute == null)
            {
                // 如果没有包含 "ts"，手动添加
                newAttributes.Add(new TdDeviceAttribute(_guidGenerator.Create())
                {
                    TdDeviceTypeId = input.SuperTableId,
                    Name = "ts",  // 默认的 TIMESTAMP 列名
                    Description = "时间戳",
                    ColumnType = "TIMESTAMP",
                    SortOrder = 0
                });
            }
            else
            {
                // 如果包含 "ts"，确保它的 SortOrder 为 0 且 ColumnType 是 TIMESTAMP
                newAttributes.Add(new TdDeviceAttribute(_guidGenerator.Create())
                {
                    TdDeviceTypeId = input.SuperTableId,
                    Name = tsAttribute.Name,
                    Description = tsAttribute.Description,
                    ColumnType = "TIMESTAMP",
                    SortOrder = 0
                });
            }
            // 添加传入的其他属性
            newAttributes.AddRange(input.Attributes
                .Where(attr => attr.Name != "ts")  // 排除已经处理过的 ts
                .Select(attr => new TdDeviceAttribute(_guidGenerator.Create())
                {
                    TdDeviceTypeId = input.SuperTableId,
                    Name = attr.Name,
                    Description = attr.Description,
                    ColumnType = attr.ColumnType,
                    SortOrder = attr.SortOrder ?? (newAttributes.Count)
                }));

            await _tdDeviceAttributeRepository.InsertManyAsync(newAttributes);
        }
        /// <summary>
        /// 删除单个属性
        /// </summary>
        /// <param name="attributeId"></param>
        /// <returns></returns>
        public async Task DeleteAttributeAsync(Guid attributeId)
        {
            // 检查列是否存在
            var attribute = await _tdDeviceAttributeRepository.FirstOrDefaultAsync(a => a.Id == attributeId);
            if (attribute == null)
            {
                throw new UserFriendlyException("未发现当前属性", "指定的属性不存在.")
                    .WithData("AttributeId", attributeId);
            }
            if (attribute.Name == "ts")
            {
                throw new UserFriendlyException("当前未默认属性", "指定的属性不允许删除.")
                    .WithData("AttributeId", attributeId);
            }
            // 删除列
            await _tdDeviceAttributeRepository.DeleteAsync(attribute);
        }
        /// <summary>
        /// 创建子表
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<Guid> CreateSubTableAsync(CreateSubTableDto input)
        {
            var subTable = new TdDeviceInstance(_guidGenerator.Create())
            {
                TdDeviceTypeId = input.SuperTableId,
                InstanceName = input.InstanceName,
                Description = input.Description
            };

            await _tdDeviceInstanceRepository.InsertAsync(subTable);
            return subTable.Id;
        }
        /// <summary>
        /// 根据超级表ID获取子表
        /// </summary>
        /// <param name="superTableId"></param>
        /// <returns></returns>
        public async Task<List<SubTableDto>> GetSubTablesBySuperTableIdAsync(Guid superTableId)
        {
            var subTables = await _tdDeviceInstanceRepository.GetAttributesBySuperTableIdAsync(superTableId);

            return subTables.Select(subTable => new SubTableDto
            {
                Id = subTable.Id,
                InstanceName = subTable.InstanceName,
                Description = subTable.Description
            }).ToList();
        }
        /// <summary>
        /// 根据id获取超级表
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public async Task<List<SubTableDto>> GetSubTablesAsync(List<Guid> ids)
        {
            var subTables = await _tdDeviceInstanceRepository.GetAttributesByIdAsync(ids);
            return subTables.Select(subTable => new SubTableDto
            {
                Id = subTable.Id,
                InstanceName = subTable.InstanceName,
                Description = subTable.Description
            }).ToList();
        }
        /// <summary>
        /// 删除单个子表
        /// </summary>
        /// <param name="subTableId"></param>
        /// <returns></returns>
        public async Task DeleteSubTableAsync(Guid subTableId)
        {
            // 检查子表是否存在
            var subTable = await _tdDeviceInstanceRepository.FirstOrDefaultAsync(s => s.Id == subTableId);
            if (subTable == null)
            {
                throw new UserFriendlyException("未发现当前子表", "指定的子表不存在.")
                    .WithData("SubTableId", subTableId);
            }

            // 删除子表
            await _tdDeviceInstanceRepository.DeleteAsync(subTable);
        }
        /// <summary>
        /// 根据超级表或子表ID获取属性
        /// </summary>
        /// <param name="tableId"></param>
        /// <param name="isSuperTable">是否超级表</param>
        /// <returns></returns>
        public async Task<List<AttributeDto>> GetAttributesByTableIdAsync(Guid tableId, bool isSuperTable)
        {
            var attributes = await _tdDeviceManager.GetAttributes(tableId, isSuperTable);

            return attributes.OrderBy(attr => attr.SortOrder)
                .Select(attr => new AttributeDto
                {
                    id = attr.Id,
                    Name = attr.Name,
                    Description = attr.Description,
                    ColumnType = attr.ColumnType,
                    SortOrder = attr.SortOrder
                }).ToList();
        }

        /// <summary>
        /// 创建或更新测点与子表实例关系
        /// </summary>
        /// <param name="relations"></param>
        /// <returns></returns>
        /// <exception cref="ArgumentNullException"></exception>
        public async Task CreateOrUpdatePointRelationAsync(List<PointRelationDto> relations)
        {

            if (relations == null || !relations.Any())
            {
                throw new ArgumentException("测点与子表的关系集合不能为空", nameof(relations));
            }
            var pointIds = relations.Select(r => r.PointId).ToList();
            var subTableIds = relations.Select(r => r.SubTableId).ToList();

            // 查询所有已存在的绑定关系
            var existingRelations = await _tdDevicePointRelationRepository.GetRelationsAsync(pointIds);

            // 查询所有子表实例是否存在
            var subTables = await _tdDeviceInstanceRepository.GetAttributesByIdAsync(subTableIds);
            if (subTables.Count != subTableIds.Count)
            {
                throw new UserFriendlyException("部分子表实例不存在").WithData("InvalidSubTableIds", subTableIds.Except(subTables.Select(s => s.Id)).ToList());
            }

            // 查询所有测点是否存在
            var points = await _pointRepository.GetPointByIdAsync(pointIds);
            if (points.Count != pointIds.Count)
            {
                throw new UserFriendlyException("部分测点不存在").WithData("InvalidPointIds", pointIds.Except(points.Select(p => p.Id)).ToList());
            }

            // 新建的关系集合
            var newRelations = new List<TdDevicePointRelation>();

            // 更新的关系集合
            foreach (var (pointId, subTableId) in pointIds.Zip(subTableIds, (p, s) => (p, s)))
            {
                var existingRelation = existingRelations.FirstOrDefault(r => r.PointId == pointId);
                if (existingRelation != null)
                {
                    // 更新已存在的关系
                    existingRelation.TdDeviceInstanceId = subTableId;
                }
                else
                {
                    // 创建新的关系
                    newRelations.Add(new TdDevicePointRelation
                    {
                        PointId = pointId,
                        TdDeviceInstanceId = subTableId
                    });
                }
            }

            // 批量更新已存在的关系
            if (existingRelations.Any())
            {
                await _tdDevicePointRelationRepository.UpdateRangeAsync(existingRelations);
            }

            // 批量插入新的关系
            if (newRelations.Any())
            {
                await _tdDevicePointRelationRepository.AddRangeAsync(newRelations);
            }
        }
        /// <summary>
        /// 根据子表实例 ID 获取所有测点关系
        /// </summary>
        /// <param name="subTableId">子表实例 ID</param>
        /// <returns>与指定子表关联的测点关系列表</returns>
        public async Task<List<PointRelationDto>> GetRelationsByInstanceIdAsync(Guid subTableId)
        {
            var relations = await _tdDevicePointRelationRepository.GetRelationsByInstanceIdAsync(subTableId);
            return ObjectMapper.Map<List<TdDevicePointRelation>, List<PointRelationDto>>(relations);
        }

        /// <summary>
        /// 获取测点与子表实例的关联关系集合。
        /// </summary>
        /// <param name="pointIds">测点集合</param>
        /// <returns></returns>
        public async Task<List<PointRelationDto>> GetRelationsAsync(List<Guid> pointIds)
        {
            var relation = await _tdDevicePointRelationRepository.GetRelationsAsync(pointIds);
            return ObjectMapper.Map<List<TdDevicePointRelation>, List<PointRelationDto>>(relation);
        }

        /// <summary>
        /// 删除测点与子表实例的关系--测点删除时执行
        /// </summary>
        /// <param name="pointId">测点 ID</param>
        public async Task DeleteRelationsAsync(List<Guid> pointIds)
        {
            var relation = await _tdDevicePointRelationRepository.GetRelationsAsync(pointIds);
            if (relation != null)
            {
                await _tdDevicePointRelationRepository.DeleteRangeAsync(relation);
            }
        }
        /// <summary>
        ///  获取超级表和子表的结构--全部的数据
        /// </summary>
        /// <returns></returns>
        public async Task<List<SuperTableDto>> GetAllSuperTablesWithSubTablesAsync()
        {
            var superTables = await _tdDeviceTypeRepository.WithDetailsAsync(x => x.Attributes, x => x.Instances);
            return superTables.Select(superTable => new SuperTableDto
            {
                Id = superTable.Id,
                Name = superTable.Name,
                Description = superTable.Description,
                Attributes = superTable.Attributes.Select(attr => new AttributeDto
                {
                    Name = attr.Name,
                    Description = attr.Description,
                    ColumnType = attr.ColumnType
                }).ToList(),
                Instances = superTable.Instances.Select(instance => new SubTableDto
                {
                    Id = instance.Id,
                    InstanceName = instance.InstanceName,
                    Description = instance.Description
                }).ToList()
            }).ToList();
        }
        /// <summary>
        /// 根据数据库名称生成超级表和子表的SQL脚本
        /// </summary>
        /// <param name="dbName">数据库名称</param>
        /// <returns></returns>
        public async Task GenerateSqlScriptsAsync(string dbName)
        {
            // 获取数据库信息
            var database = await _tdDatabaseRepository.FirstOrDefaultAsync(db => db.Name == dbName);
            if (database == null)
            {
                throw new UserFriendlyException("数据库不存在", "指定的数据库名称无效。").WithData("DatabaseName", dbName);
            }

            // 获取超级表及其属性
            var superTables = await _tdDeviceTypeRepository.GetSuperByDataBaseTableIdAsync(database.Id);
            var sqlScripts = new List<TdSqlScriptDto>();

            // 生成创建数据库的 SQL 脚本
            var createDatabaseScript = $"CREATE DATABASE {dbName};";

            sqlScripts.Add(new TdSqlScriptDto
            {
                DbName = dbName,
                SuperName = string.Empty,
                SqlScript = createDatabaseScript,
                SortOrder = 0
            });

            // 生成超级表的 SQL 脚本
            foreach (var superTable in superTables)
            {
                var superName = superTable.Name;

                // 获取超级表的列
                var attributes = await _tdDeviceAttributeRepository.GetAttributesBySuperTableIdAsync(superTable.Id);
                var sortedAttributes = attributes.OrderBy(attr => attr.SortOrder).ToList();  // 按 SortOrder 排序
                var columns = string.Join(", ", sortedAttributes.Select(attr => $"{attr.Name} {attr.ColumnType}"));

                // 生成超级表的 SQL 脚本
                var superTableScript = $"CREATE STABLE {dbName}.{superName} ({columns}) TAGS (id VARCHAR(36));";

                sqlScripts.Add(new TdSqlScriptDto
                {
                    DbName = dbName,
                    SuperName = superName,
                    SqlScript = superTableScript,
                    SortOrder = 1
                });
            }
            //生成子表的 SQL 脚本
            foreach (var superTable in superTables)
            {
                var superName = superTable.Name;
                // 获取子表
                var subTables = await _tdDeviceInstanceRepository.GetAttributesBySuperTableIdAsync(superTable.Id);
                foreach (var subTable in subTables)
                {
                    var subTableScript = $"CREATE TABLE {dbName}.{subTable.InstanceName} USING {dbName}.{superName} TAGS ('{subTable.InstanceName}');";

                    sqlScripts.Add(new TdSqlScriptDto
                    {
                        DbName = dbName,
                        SuperName = superName,
                        SqlScript = subTableScript,
                        SortOrder = 2
                    });
                }
            }

            // 按照 SortOrder 排序 SQL 脚本，确保执行顺序
            sqlScripts = sqlScripts.OrderBy(script => script.SortOrder).ToList();

            // 删除已存在的当前数据库的脚本
            await _tdSqlScriptRepository.DeleteByDbNameAsync(dbName);

            // 插入新的脚本
            foreach (var script in sqlScripts)
            {
                await _tdSqlScriptRepository.InsertAsync(new TdSqlScript(_guidGenerator.Create())
                {
                    DbName = script.DbName,
                    SuperName = script.SuperName,
                    SqlScript = script.SqlScript,
                    SortOrder = script.SortOrder
                });
            }
            //return sqlScripts;
        }
        /// <summary>
        /// 获取TdEngine属性列类型
        /// </summary>
        /// <returns></returns>
        public List<OperationOptionInt> GetTdEngineAttributeDataTypes()
        {
            var localizedName = $"TdEngineAttributeEnum";

            var options = _tdDeviceManager.GetTdEngineAttributeDataTypes().Select(item => new OperationOptionInt(item.Key, L[$"{localizedName}:{item.Value?.ToString()}"])).ToList();

            return options;
        }

        /// <summary>
        /// 生成种子数据
        /// </summary>
        /// <param name="dbPrefix">数据库前缀</param>
        /// <param name="vesselId">船舶ID</param>
        /// <param name="dbDescription">数据库描述</param>
        /// <returns></returns>
        public async Task<bool> TdDataSeedAsync(DbPrefixEnum dbPrefix, Guid vesselId, string dbDescription)
        {
            // 动态获取 Manager
            var manager = _factory.GetManager(dbPrefix);

            // 调用对应的 Manager 
            return await manager.SeedDataAsync(vesselId, dbDescription);
        }
    }
}
